using System;
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;
using Microsoft.Data.SqlClient;
using YushengMall.Backend.Api.Database;

namespace YushengMall.Backend.Api.Procedure
{
    public class ExecProcedure
    {
        private readonly static string connectSql = "server=.;database=yusheng_mall_users;uid=sa;pwd=123456;";

        private string ResSqlcommand(Dictionary<string,object> dict,string proc)
        {
            var sqlcommand ="Exec " + proc+ " ";
            var parameter = "";
            var num = 0;
            foreach (var item in dict)
            {
                num ++;
                var key = item.Key;
                var value = item.Value;
                
                if(num == dict.Count){
                    parameter +=key+"="+value;
                }else{
                    parameter +=key+"="+value+",";
                }
                
            }
            return sqlcommand + parameter;
        }
        //执行存储过程，返回查询结果
        public DataTable ProcQueryData(Dictionary<string,object> dict,string proc)
        {
                var SqlCommandStr = ResSqlcommand(dict,proc);
                var res = DbHelper.QueryTable(SqlCommandStr);
                return res;
        }

        //执行存储过程返回受影响的行数
        public int ProcUpdateData(Dictionary<string,object> dict,string proc)
        {
            var SqlCommandStr = ResSqlcommand(dict,proc);
            var res = DbHelper.UpdateOrDelTable(SqlCommandStr);
            return res;
        }

        //执行存储过程返回结果的行数
        public int ProcIsExistence(Dictionary<string,object> dict,string proc)
        {
            var SqlcommandStr = ResSqlcommand(dict,proc);
            var res = DbHelper.IsExistence(SqlcommandStr);
            return res;
        }
        

             public DataTable ProcQueryDataAsync(Dictionary<string,object> dict,string proc)
        {
                var SqlCommandStr = ResSqlcommand(dict,proc);
                SqlConnection sqlConnection = new SqlConnection(connectSql);
                sqlConnection.Open();
                string sql = SqlCommandStr;
                DataTable dataSet = new DataTable();
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sql, sqlConnection);
                sqlDataAdapter.Fill(dataSet);
                sqlConnection.Close();
                return dataSet;
        }

        public async Task<Task<int>> ProcUpdateDataAsync(Dictionary<string,object> dict,string proc)
        {
            var SqlCommandStr = ResSqlcommand(dict,proc);
            SqlConnection sqlConnection = new SqlConnection(connectSql);
            SqlCommand sqlCommand = new SqlCommand(SqlCommandStr,sqlConnection);
            await sqlConnection.OpenAsync();
            var res = sqlCommand.ExecuteNonQueryAsync();
            return res;
        }


        public async Task<int> ProcIsExistenceAsync(Dictionary<string,object> dict,string proc)
        {
            var SqlcommandStr = ResSqlcommand(dict,proc);
            SqlConnection sqlConnection = new SqlConnection();
            SqlCommand sqlCommand = new SqlCommand();
            await sqlConnection.OpenAsync();
            var i = Convert.ToInt32(sqlCommand.ExecuteScalar());
            return i;
        }

    }
}